// 订单管理路由
const express = require('express');
const router = express.Router();
const { query, transaction } = require('../config/database');
const { authenticateToken, requireAdmin, optionalAuth } = require('../middleware/auth');
const { validateOrder, validateId, validatePagination } = require('../middleware/validation');
const { calculatePagination, generateOrderNumber, calculateHours, formatAmount } = require('../utils/helpers');
const ResponseHelper = require('../utils/response');

// 创建订单
router.post('/', authenticateToken, validateOrder, async (req, res) => {
  try {
    const userId = req.user.id;
    const { table_id, booking_date, start_time, end_time, phone, remark } = req.body;

    // 检查台球桌是否存在且可用
    const tables = await query(
      'SELECT id, name, type, hourly_rate, status FROM tables WHERE id = ?',
      [table_id]
    );

    if (tables.length === 0) {
      return ResponseHelper.notFound(res, '台球桌不存在');
    }

    const table = tables[0];
    if (table.status === 'maintenance') {
      return ResponseHelper.error(res, '台球桌维护中，暂不可用', 400);
    }

    // 检查时间段是否已被预订
    const conflictOrders = await query(
      `SELECT id FROM orders 
       WHERE table_id = ? 
         AND booking_date = ? 
         AND status IN ('pending', 'confirmed')
         AND (
           (start_time <= ? AND end_time > ?) OR
           (start_time < ? AND end_time >= ?) OR
           (start_time >= ? AND end_time <= ?)
         )`,
      [table_id, booking_date, start_time, start_time, end_time, end_time, start_time, end_time]
    );

    if (conflictOrders.length > 0) {
      return ResponseHelper.error(res, '该时间段已被预订', 400);
    }

    // 计算时长和费用
    const startDateTime = new Date(`${booking_date} ${start_time}`);
    const endDateTime = new Date(`${booking_date} ${end_time}`);
    const duration = calculateHours(startDateTime, endDateTime);
    const totalCost = duration * parseFloat(table.hourly_rate);

    // 检查用户余额是否充足
    if (parseFloat(req.user.balance) < totalCost) {
      return ResponseHelper.error(res, '余额不足，请先充值', 400);
    }

    // 生成订单号
    const orderNumber = generateOrderNumber();

    await transaction(async (connection) => {
      // 创建订单
      const orderResult = await connection.execute(
        `INSERT INTO orders (
          order_number, user_id, table_id, table_name, table_type,
          booking_date, start_time, end_time, duration, total_cost,
          phone, remark, status, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())`,
        [
          orderNumber, userId, table_id, table.name, table.type,
          booking_date, start_time, end_time, duration, totalCost,
          phone, remark || ''
        ]
      );

      // 扣除用户余额
      await connection.execute(
        'UPDATE users SET balance = balance - ?, updated_at = NOW() WHERE id = ?',
        [totalCost, userId]
      );

      // 更新台球桌状态为占用（如果是当前时间段）
      const now = new Date();
      const bookingStart = new Date(`${booking_date} ${start_time}`);
      const bookingEnd = new Date(`${booking_date} ${end_time}`);

      if (now >= bookingStart && now <= bookingEnd) {
        await connection.execute(
          'UPDATE tables SET status = "occupied", updated_at = NOW() WHERE id = ?',
          [table_id]
        );
      }

      return orderResult.insertId;
    });

    // 获取创建的订单信息
    const newOrder = await query(
      `SELECT o.*, u.username, u.real_name 
       FROM orders o 
       LEFT JOIN users u ON o.user_id = u.id 
       WHERE o.order_number = ?`,
      [orderNumber]
    );

    ResponseHelper.success(res, newOrder[0], '订单创建成功', 201);

  } catch (error) {
    console.error('创建订单错误:', error);
    ResponseHelper.serverError(res, '创建订单失败');
  }
});

// 获取所有订单（管理员）
router.get('/', authenticateToken, requireAdmin, validatePagination, async (req, res) => {
  try {
    const { page, pageSize, offset, limit } = calculatePagination(req.query.page, req.query.pageSize);
    const { status, table_id, user_id, date_from, date_to, search } = req.query;

    // 构建查询条件
    let whereClause = 'WHERE 1=1';
    let queryParams = [];

    if (status) {
      whereClause += ' AND o.status = ?';
      queryParams.push(status);
    }

    if (table_id) {
      whereClause += ' AND o.table_id = ?';
      queryParams.push(table_id);
    }

    if (user_id) {
      whereClause += ' AND o.user_id = ?';
      queryParams.push(user_id);
    }

    if (date_from) {
      whereClause += ' AND o.booking_date >= ?';
      queryParams.push(date_from);
    }

    if (date_to) {
      whereClause += ' AND o.booking_date <= ?';
      queryParams.push(date_to);
    }

    if (search) {
      whereClause += ' AND (o.order_number LIKE ? OR u.username LIKE ? OR u.phone LIKE ?)';
      const searchPattern = `%${search}%`;
      queryParams.push(searchPattern, searchPattern, searchPattern);
    }

    // 获取订单总数
    const countResult = await query(
      `SELECT COUNT(*) as total 
       FROM orders o 
       LEFT JOIN users u ON o.user_id = u.id 
       ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    // 获取订单列表
    const orders = await query(
      `SELECT o.*, u.username, u.real_name, u.phone as user_phone
       FROM orders o 
       LEFT JOIN users u ON o.user_id = u.id 
       ${whereClause} 
       ORDER BY o.created_at DESC 
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    ResponseHelper.paginated(res, orders, total, page, pageSize, '获取订单列表成功');

  } catch (error) {
    console.error('获取订单列表错误:', error);
    ResponseHelper.serverError(res, '获取订单列表失败');
  }
});

// 获取用户订单列表
router.get('/user', authenticateToken, validatePagination, async (req, res) => {
  try {
    const userId = req.user.id;
    const { page, pageSize, offset, limit } = calculatePagination(req.query.page, req.query.pageSize);
    const { status, date_from, date_to } = req.query;

    // 构建查询条件
    let whereClause = 'WHERE user_id = ?';
    let queryParams = [userId];

    if (status) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    if (date_from) {
      whereClause += ' AND booking_date >= ?';
      queryParams.push(date_from);
    }

    if (date_to) {
      whereClause += ' AND booking_date <= ?';
      queryParams.push(date_to);
    }

    // 获取订单总数
    const countResult = await query(
      `SELECT COUNT(*) as total FROM orders ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    // 获取订单列表
    const orders = await query(
      `SELECT * FROM orders ${whereClause} 
       ORDER BY created_at DESC 
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    ResponseHelper.paginated(res, orders, total, page, pageSize, '获取用户订单列表成功');

  } catch (error) {
    console.error('获取用户订单列表错误:', error);
    ResponseHelper.serverError(res, '获取用户订单列表失败');
  }
});

// 获取已预订时间段
router.get('/booked-slots', optionalAuth, async (req, res) => {
  try {
    const { table_id, date } = req.query;

    if (!table_id || !date) {
      return ResponseHelper.error(res, '台球桌ID和日期参数必填', 400);
    }

    const bookedSlots = await query(
      `SELECT start_time, end_time, status
       FROM orders
       WHERE table_id = ?
         AND booking_date = ?
         AND status IN ('pending', 'confirmed', 'using', 'paid')
       ORDER BY start_time`,
      [table_id, date]
    );

    ResponseHelper.success(res, bookedSlots, '获取已预订时间段成功');

  } catch (error) {
    console.error('获取已预订时间段错误:', error);
    ResponseHelper.serverError(res, '获取已预订时间段失败');
  }
});

// 获取订单详情
router.get('/:id', authenticateToken, validateId, async (req, res) => {
  try {
    const orderId = req.params.id;
    const userId = req.user.id;
    const isAdmin = req.user.role === 'admin';

    // 构建查询条件（普通用户只能查看自己的订单）
    let whereClause = 'WHERE o.id = ?';
    let queryParams = [orderId];

    if (!isAdmin) {
      whereClause += ' AND o.user_id = ?';
      queryParams.push(userId);
    }

    const orders = await query(
      `SELECT o.*, u.username, u.real_name, u.phone as user_phone
       FROM orders o
       LEFT JOIN users u ON o.user_id = u.id
       ${whereClause}`,
      queryParams
    );

    if (orders.length === 0) {
      return ResponseHelper.notFound(res, '订单不存在');
    }

    ResponseHelper.success(res, orders[0], '获取订单详情成功');

  } catch (error) {
    console.error('获取订单详情错误:', error);
    ResponseHelper.serverError(res, '获取订单详情失败');
  }
});

// 更新订单状态
router.put('/:id/status', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const orderId = req.params.id;
    const { status } = req.body;

    if (!['pending', 'confirmed', 'using', 'completed', 'cancelled'].includes(status)) {
      return ResponseHelper.error(res, '订单状态无效', 400);
    }

    // 检查订单是否存在
    const orders = await query('SELECT * FROM orders WHERE id = ?', [orderId]);
    if (orders.length === 0) {
      return ResponseHelper.notFound(res, '订单不存在');
    }

    const order = orders[0];

    await transaction(async (connection) => {
      // 更新订单状态
      await connection.execute(
        'UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, orderId]
      );

      // 根据状态变化处理相关逻辑
      if (status === 'cancelled' && order.status !== 'cancelled') {
        // 取消订单：退还用户余额
        await connection.execute(
          'UPDATE users SET balance = balance + ?, updated_at = NOW() WHERE id = ?',
          [order.total_cost, order.user_id]
        );

        // 释放台球桌
        await connection.execute(
          'UPDATE tables SET status = "available", updated_at = NOW() WHERE id = ?',
          [order.table_id]
        );
      } else if (status === 'completed') {
        // 完成订单：释放台球桌，增加用户游戏时长
        await connection.execute(
          'UPDATE tables SET status = "available", updated_at = NOW() WHERE id = ?',
          [order.table_id]
        );

        await connection.execute(
          'UPDATE users SET play_time = play_time + ?, updated_at = NOW() WHERE id = ?',
          [order.duration, order.user_id]
        );
      } else if (status === 'confirmed') {
        // 确认订单：占用台球桌
        await connection.execute(
          'UPDATE tables SET status = "occupied", updated_at = NOW() WHERE id = ?',
          [order.table_id]
        );
      } else if (status === 'using') {
        // 开始使用：确保台球桌状态为占用
        await connection.execute(
          'UPDATE tables SET status = "occupied", updated_at = NOW() WHERE id = ?',
          [order.table_id]
        );
      }
    });

    ResponseHelper.success(res, {
      order_id: orderId,
      old_status: order.status,
      new_status: status
    }, '订单状态更新成功');

  } catch (error) {
    console.error('更新订单状态错误:', error);
    ResponseHelper.serverError(res, '更新订单状态失败');
  }
});

// 取消订单
router.put('/:id/cancel', authenticateToken, validateId, async (req, res) => {
  try {
    const orderId = req.params.id;
    const userId = req.user.id;
    const isAdmin = req.user.role === 'admin';

    // 构建查询条件（普通用户只能取消自己的订单）
    let whereClause = 'WHERE id = ?';
    let queryParams = [orderId];

    if (!isAdmin) {
      whereClause += ' AND user_id = ?';
      queryParams.push(userId);
    }

    const orders = await query(`SELECT * FROM orders ${whereClause}`, queryParams);
    if (orders.length === 0) {
      return ResponseHelper.notFound(res, '订单不存在');
    }

    const order = orders[0];

    // 检查订单状态是否可以取消
    if (!['pending', 'confirmed', 'using'].includes(order.status)) {
      return ResponseHelper.error(res, '订单状态不允许取消', 400);
    }

    await transaction(async (connection) => {
      // 更新订单状态为已取消
      await connection.execute(
        'UPDATE orders SET status = "cancelled", updated_at = NOW() WHERE id = ?',
        [orderId]
      );

      // 退还用户余额
      await connection.execute(
        'UPDATE users SET balance = balance + ?, updated_at = NOW() WHERE id = ?',
        [order.total_cost, order.user_id]
      );

      // 释放台球桌
      await connection.execute(
        'UPDATE tables SET status = "available", updated_at = NOW() WHERE id = ?',
        [order.table_id]
      );
    });

    ResponseHelper.success(res, {
      order_id: orderId,
      refund_amount: formatAmount(order.total_cost)
    }, '订单取消成功，余额已退还');

  } catch (error) {
    console.error('取消订单错误:', error);
    ResponseHelper.serverError(res, '取消订单失败');
  }
});

// 完成订单
router.put('/:id/complete', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const orderId = req.params.id;

    const orders = await query('SELECT * FROM orders WHERE id = ?', [orderId]);
    if (orders.length === 0) {
      return ResponseHelper.notFound(res, '订单不存在');
    }

    const order = orders[0];

    if (!['confirmed', 'using'].includes(order.status)) {
      return ResponseHelper.error(res, '只能完成已确认或使用中的订单', 400);
    }

    await transaction(async (connection) => {
      // 更新订单状态为已完成
      await connection.execute(
        'UPDATE orders SET status = "completed", updated_at = NOW() WHERE id = ?',
        [orderId]
      );

      // 释放台球桌
      await connection.execute(
        'UPDATE tables SET status = "available", updated_at = NOW() WHERE id = ?',
        [order.table_id]
      );

      // 增加用户游戏时长
      await connection.execute(
        'UPDATE users SET play_time = play_time + ?, updated_at = NOW() WHERE id = ?',
        [order.duration, order.user_id]
      );
    });

    ResponseHelper.success(res, {
      order_id: orderId,
      play_time_added: order.duration
    }, '订单完成成功');

  } catch (error) {
    console.error('完成订单错误:', error);
    ResponseHelper.serverError(res, '完成订单失败');
  }
});

// 删除订单（管理员）
router.delete('/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const orderId = req.params.id;

    const orders = await query('SELECT * FROM orders WHERE id = ?', [orderId]);
    if (orders.length === 0) {
      return ResponseHelper.notFound(res, '订单不存在');
    }

    const order = orders[0];

    // 只能删除已取消或已完成的订单
    if (!['cancelled', 'completed'].includes(order.status)) {
      return ResponseHelper.error(res, '只能删除已取消或已完成的订单', 400);
    }

    await query('DELETE FROM orders WHERE id = ?', [orderId]);

    ResponseHelper.success(res, null, '订单删除成功');

  } catch (error) {
    console.error('删除订单错误:', error);
    ResponseHelper.serverError(res, '删除订单失败');
  }
});

module.exports = router;
